package com.cskaoyan.mall.dao;

import com.alibaba.druid.util.StringUtils;
import com.cskaoyan.mall.model.comment.Comment;
import com.cskaoyan.mall.model.goods.Spec;
import com.cskaoyan.mall.model.orders.*;
import com.cskaoyan.mall.util.DruidUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @author: jyf2000jyf
 */
public class OrderDaoImpl implements OrderDao {
    @Override
    public int selectTotal(OrdersByPageBO ordersByPageBO) {
        Map<String, Object> result = getDynamicSql(ordersByPageBO);
        String sql = (String) result.get("sql");
        List<Object> params = (List<Object>) result.get("params");
        sql = "select count(*) from orders where 1 = 1" + sql;
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
        Long query = null;
        try {
            query = queryRunner.query(sql, new ScalarHandler<>(), params.toArray());
        } catch (
                SQLException e) {
            e.printStackTrace();
        }
        return query.intValue();
    }

    @Override
    public List<OrdersByPageVOInnerOrderInfo> orderByPage(OrdersByPageBO ordersByPageBO) {
        Map<String, Object> result = getDynamicSql(ordersByPageBO);
        String sql = (String) result.get("sql");
        List<Object> params = (List<Object>) result.get("params");
        // 代表limit值，即一页有多少数据
        params.add(ordersByPageBO.getPagesize());
        // 代表offset值，要跳过前面几页的值
        params.add((ordersByPageBO.getCurrentPage() - 1) * ordersByPageBO.getPagesize());
        sql = "select id,userId,specId as goodsDetailId,goodsName as goods,specName as spec,number as goodsNum,amount,stateId,nickname,receiver as name,address,telephone as phone,updatetime as time from orders where 1 = 1" + sql + " limit ? offset ?";
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
        List<OrdersByPageVOInnerOrderInfo> orders = null;
        try {
            orders = queryRunner.query(sql, new BeanListHandler<>(OrdersByPageVOInnerOrderInfo.class), params.toArray());
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return orders;
    }

    @Override
    public boolean deleteOrder(int id) {
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
        try {
            queryRunner.update("delete from orders where id = ?", id);
        } catch (SQLException e) {
            return false;
        }
        return true;
    }

    @Override
    public List<Spec> getSpecByOrderId(int orderId) {
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
        List<Spec> specList = null;
        try {
            specList = queryRunner.query("select id,specName,unitPrice from spec where goodsId = (select goodsId from spec where id = (select specId from orders where id = ?))", new BeanListHandler<>(Spec.class), orderId);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return specList;
    }

    @Override
    public OrderByIdVO getOrderById(int orderId) {
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
        OrderByIdVO orderByIdVO = null;
        try {
            orderByIdVO = queryRunner.query("select id,amount,number as num,specId as goodsDetailId,stateId as state,goodsName as goods,stateId as currentState,specId as currentSpec from orders where id = ?", new BeanHandler<>(OrderByIdVO.class), orderId);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return orderByIdVO;
    }

    @Override
    public boolean changeOrder(changeOrderBO changeOrderBO) {
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
        OrderByIdVO orderByIdVO = null;
        try {
            queryRunner.update("update orders set updatetime = curtime(),number = ?,specId = ?,specName = (select specName from spec where id = ?),stateId = ? where id = ?", changeOrderBO.getNum(), changeOrderBO.getSpec(), changeOrderBO.getSpec(), changeOrderBO.getState(), Integer.parseInt(changeOrderBO.getId()));
        } catch (SQLException e) {
            return false;
        }
        return true;
    }

    @Override
    public List<OrderByStateVO> getOrderByState(Integer state, Integer userId) {
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
        List<OrderByStateVO> orderByStateVOList = null;
        try {
            orderByStateVOList = queryRunner.query("select id,amount,createtime,specId as goodsDetailId,number as goodsNum,hasComment,stateId as state from orders where userId = ? and stateId = ?", new BeanListHandler<>(OrderByStateVO.class), userId, state);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return orderByStateVOList;
    }

    @Override
    public List<OrderByStateVO> getOrderByState(Integer userId) {
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
        List<OrderByStateVO> orderByStateVOList = null;
        try {
            orderByStateVOList = queryRunner.query("select id,amount,createtime,specId as goodsDetailId,number as goodsNum,hasComment,stateId as state from orders where userId = ?", new BeanListHandler<>(OrderByStateVO.class), userId);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return orderByStateVOList;
    }

    @Override
    public boolean pay(Integer id) {
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
        try {
            queryRunner.update("update orders set stateId = 1 where id = ?", id);
        } catch (SQLException e) {
            return false;
        }
        return true;
    }

    @Override
    public boolean confirmReceive(Integer id) {
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
        try {
            queryRunner.update("update orders set stateId = 3 where id = ?", id);
        } catch (SQLException e) {
            return false;
        }
        return true;
    }

    @Override
    public boolean settleAccounts(CartList cartList) {
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
        List<SettleAccounts> CartList = cartList.getCartList();
        for (SettleAccounts orders : CartList) {
            try {
                queryRunner.update("update orders set stateId = 1,number = ?,amount = ? where id = ?", orders.getGoodsNum(), orders.getAmount(), orders.getId());
            } catch (SQLException e) {
                return false;
            }
        }
        return true;
    }

    @Override
    public boolean addOrder(Orders orders) {
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
        try {
            queryRunner.update("insert into orders values(null,?,?,?,?,?,?,?,?,?,?,?,?,curtime(),curtime(),0)",orders.getUserId(),orders.getNickname(),orders.getReceiver(),
                    orders.getAddress(),orders.getTelephone(),orders.getGoodsName(),orders.getGoodsId(),orders.getSpecName(),orders.getSpecId(),orders.getNumber(),orders.getAmount(),orders.getStateId());
        } catch (SQLException e) {
            return false;
        }
        return true;
    }

    @Override
    public GoodsAndSpecs getGoodsAndSpecsBySpecId(Integer goodsDetailId) {
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
        GoodsAndSpecs goodsAndSpecs = null;
        try {
            goodsAndSpecs = queryRunner.query("select spec.id as specId,specName,name as goodsName,goodsId from goods,spec where goods.id = goodsId and spec.id = ?", new BeanHandler<>(GoodsAndSpecs.class), goodsDetailId);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return goodsAndSpecs;
    }

    @Override
    public String getSpecNameByOrderId(Integer ordersId) {
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
        String specName = null;
        try{
            Orders orders = queryRunner.query("select * from orders where id = ?", new BeanHandler<>(Orders.class), ordersId);
            specName = orders.getSpecName();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return specName;
    }

    @Override
    public boolean sendComment(Comment comment) {
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
        try{
            queryRunner.update("insert into comment values(null,?,?,?,?,?,?,curtime())", comment.getUserId(),comment.getUsername(),comment.getGoodsId(),
                    comment.getSpecName(),comment.getScore(),comment.getComment());
        } catch (SQLException e) {
            return false;
        }
        return true;
    }

    @Override
    public boolean setHasCommentByOrderId(Integer ordersId) {
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
        try{
            queryRunner.update("update orders set hasComment = 1 where id = ?",ordersId);
        } catch (SQLException e) {
            return false;
        }
        return true;
    }

    private Map<String, Object> getDynamicSql(OrdersByPageBO ordersByPageBO) {
        String sql = "";
        List<Object> params = new ArrayList<>();
        if (!StringUtils.isEmpty(ordersByPageBO.getAddress())) {
            sql = sql + " and address like ?";
            params.add("%" + ordersByPageBO.getAddress() + "%");
        }
        if (!StringUtils.isEmpty(ordersByPageBO.getGoods())) {
            sql = sql + " and goodsName like ?";
            params.add("%" + ordersByPageBO.getGoods() + "%");
        }
        if (!StringUtils.isEmpty(ordersByPageBO.getId())) {
            sql = sql + " and id = ?";
            params.add(Integer.parseInt(ordersByPageBO.getId()));
        }
        if (!StringUtils.isEmpty(ordersByPageBO.getMoneyLimit1())) {
            sql = sql + " and amount <= ?";
            params.add(Double.parseDouble(ordersByPageBO.getMoneyLimit1()));
        }
        if (!StringUtils.isEmpty(ordersByPageBO.getMoneyLimit2())) {
            sql = sql + " and amount >= ?";
            params.add(Double.parseDouble(ordersByPageBO.getMoneyLimit2()));
        }
        if (!StringUtils.isEmpty(ordersByPageBO.getName())) {
            sql = sql + " and nickname like ?";
            params.add("%" + ordersByPageBO.getName() + "%");
        }
        if (ordersByPageBO.getState() != -1) {
            sql = sql + " and stateId = ?";
            params.add(ordersByPageBO.getState());
        }
        Map<String, Object> map = new HashMap<>();
        map.put("sql", sql);
        map.put("params", params);
        return map;
    }
}
